#!/usr/bin/env python
# MySQL Performance Tool

#Import modules which we need
import sys,time,platform
try:
        import MySQLdb,ConfigParser
        from optparse import OptionParser
        import os
except ImportError:
        print >> sys.stderr, """\

There was a problem importing one of the Python modules required to run yum. 
The error leading to this problem was:

%s

Please install a package which provides this module, or
verify that the module is installed correctly.

It's possible that the above module doesn't match the current version of Python, 
which is:

%s

""" % (sys.exc_value, sys.version)
        sys.exit(1)

my_name = "myperf"
my_version="0.1-beta"
mode = "top"
interval = 3
# get the platform type
sysstr = platform.system()
if(sysstr == "Windows"):
    is_windows = 1
else:
    is_windows = 0

class DatabaseSource:
    "Database info which you want to stat"
    def __init__(self,host,user,passwd,port=3306):
        self.host       = host
        self.username   = user
        self.passwd     = passwd
        self.port       = port
        
    def initForSnapReport(self,globalVars,status_now,status_old,globalWait_now,globalWait_old,fileByFile_now,fileByFile_old):
        self.globalVars = globalVars
        self.globalStatus = status_now
        self.globalStatus_old = status_old
        self.globalWaitEventList = globalWait_now
        self.globalWaitEventList_old = globalWait_old
        self.globalWaitEventsDiffList = self._diffGlobalWaitEvents(globalWait_now,globalWait_old)
        self.fileIOByFileList = fileByFile_now
        self.fileIOByFileList_old = fileByFile_old
        self.fileIOFilesDiffList = self._diffGlobalIOByFiles(fileByFile_now,fileByFile_old)
        self.hasInnoDB = self._hasInnoDB()
        self.qCache = self._isQCacheOn()
        self._version()
    
    def initDS(self):
        self.conn = self._getConnection()
        self.cursor = self._getCursor()
        self.globalVars = self._queryGlobalVars()
        self._version()
    
    def initPerf(self):
        self.globalStatus_old = self._queryGlobalStatus()
        self.globalStatus = self.globalStatus_old
        self.engines    = self._getEngines()
        self.qCache     = self._isQCacheOn()
        self.hasInnoDB  = self._hasInnoDB()
        self.processList= self._processList()
        if self.version_main >= 5 and self.version_middle >= 5:
            self.instanceWaitEventList = self._queryInstanceWaitEvents()
            self.globalWaitEventList = self._queryGlobalWaitEvents()
            self.fileIOByEventList = self._queryGlobalFileIOByEvents()
            self.fileIOByFileList = self._queryGlobalFileIOByFile()
            
            
    def _getConnection(self):
        try:
            conn = MySQLdb.connect(host=self.host, user=self.username,passwd=self.passwd,port=self.port)
        except Exception,e:
            print e
            sys.exit()
        return conn
    
    def _getCursor(self):
        return self.conn.cursor()

    def _queryGlobalStatus(self):
        self.cursor.execute("show global status")
        st_rs = self.cursor.fetchall()
        sts = {}
        for s in st_rs:
            sts[s[0]] = s[1] 
        return sts
    
    def setGlobalWaitEvents(self,eventList):
        self.globalWaitEventList = eventList
        
    def setInstanceWaitEvents(self,eventList):
        self.instanceWaitEventList = eventList
        
    def setGlobalFileIOByEvents(self,eventList):
        self.fileIOByEventList = eventList
        
    def setGlobalFileIOByFile(self,eventList):
        self.fileIOByFileList = eventList
        
                        
    def _queryGlobalWaitEvents(self):
        try:
            if self.version_tail >= 7: #After MySQL 5.5.7
                self.cursor.execute("select event_name,count_star,sum_timer_wait,avg_timer_wait,max_timer_wait,min_timer_wait from performance_schema.events_waits_summary_global_by_event_name order by sum_timer_wait desc")
            else: #Befor MySQL 5.5.7
                self.cursor.execute("select event_name,count_star,sum_timer_wait,avg_timer_wait,max_timer_wait,min_timer_wait from performance_schema.events_waits_summary_by_event_name order by sum_timer_wait desc")
            rs = self.cursor.fetchall()
        except Exception,e:
            print e
        self.total_wait = 0
        eventList = []
        for r in rs:
            e = {}
            e["type"] = "global"
            e["event_name"] = r[0]
            e["count_star"] = r[1]
            e["sum_wait"] = r[2]
            e["avg_wait"] = r[3]
            e["max_wait"] = r[4]
            e["min_wait"] = r[5]
            self.total_wait = self.total_wait +  e["sum_wait"]
            event = WaitEvent(e)
            eventList.append(event)
        return eventList
    
    def _queryInstanceWaitEvents(self):
        self.cursor.execute("select event_name,count_star,sum_timer_wait,avg_timer_wait,max_timer_wait,min_timer_wait,object_instance_begin from performance_schema.events_waits_summary_by_instance order by sum_timer_wait desc")
        rs = self.cursor.fetchall()
        eventList = []
        self.instance_wait = 0
        for r in rs:
            e = {}
            e["type"] = "instance"
            e["event_name"] = r[0]
            e["count_star"] = r[1]
            e["sum_wait"] = r[2]
            e["avg_wait"] = r[3]
            e["max_wait"] = r[4]
            e["min_wait"] = r[5] 
            e["object_instance_begin"] = r[6]

            event = WaitEvent(e)
            self.instance_wait = self.instance_wait +  e["sum_wait"]
            eventList.append(event)
        return eventList
    
    def _queryGlobalFileIOByEvents(self):
        self.cursor.execute("select event_name,count_read,count_write,sum_number_of_bytes_read,sum_number_of_bytes_write,(count_read + count_write) total_op,(sum_number_of_bytes_read + sum_number_of_bytes_write) total_bytes from performance_schema.file_summary_by_event_name order by total_op desc")
        rs = self.cursor.fetchall()
        eventList = []
        for r in rs:
            e = {}
            e["type"] = "event"
            e["event_name"] = r[0]
            e["count_read"] = r[1]
            e["count_write"] = r[2]
            e["bytes_read"] = r[3]
            e["bytes_write"] = r[4]
            e["total_op"] = r[5] 
            e["total_bytes"] = r[6] 
            
            event = FileEvent(e)
            self.total_file_io =+ e["total_op"]
            self.file_event_bytes =+ e["total_bytes"]
            eventList.append(event)
        return eventList
    
    def _queryGlobalFileIOByFile(self):
        self.cursor.execute("select file_name,sum(count_read) count_r,sum(count_write) count_w,sum(sum_number_of_bytes_read) bytes_r,sum(sum_number_of_bytes_write) bytes_w,sum(count_read + count_write) total_op,sum(sum_number_of_bytes_read + sum_number_of_bytes_write) total_bytes from performance_schema.file_summary_by_instance group by file_name order by total_op desc")
        rs = self.cursor.fetchall()
        eventList = []
        self.total_file_io = 0
        self.file_event_bytes = 0
        for r in rs:
            e = {}
            e["type"] = "file"
            e["file_name"] = r [0]
            #e["event_name"] = r[1]
            e["count_read"] = r[1]
            e["count_write"] = r[2]
            e["bytes_read"] = r[3]
            e["bytes_write"] = r[4]
            e["total_op"] = r[5] 
            e["total_bytes"] = r[6] 
                        
            event = FileEvent(e)
            self.total_file_io = self.total_file_io + e["total_op"]
            self.file_event_bytes = self.file_event_bytes + e["total_bytes"]
            eventList.append(event)
        return eventList
    
    def _isQCacheOn(self):
        if self.getVar("query_cache_type") == "OFF" or int(self.getVar("query_cache_size")) ==0:
            return 0
        return 1
    
    def _hasInnoDB(self):
        if self.getVar("have_innodb", 0) == "YES":
            #self.cursor.execute("select plugin_version from information_schema.plugins where plugin_name = 'InnoDB'")
            #plugin = self.cursor.fetchone()
            #self.innodbVersion = plugin[0]
            return True
        return False
        
    def _getEngines(self):
        self.cursor.execute("show engines")
        e = self.cursor.fetchall()
        engines = {}
        for engine in e:
            engines[engine[0]] = engine[1]
        return engines
        
    def _version(self):
        version_str = self.getVar("version")
        versions = version_str.split(".")
        self.version_main = int(versions[0])
        self.version_middle = int(versions[1])
        details = versions[2].split("-")
        self.version_tail = int(details[0])
        
    def _queryGlobalVars(self):
        self.cursor.execute("show global variables")
        var_rs = self.cursor.fetchall()
        varis = {}
        for v in var_rs:
            varis[v[0]] = v[1]
        return varis

    def _processList(self):
        self.processCount = self.cursor.execute("select id,user,host,db,command,time,state,info from information_schema.processlist where user not in('event_scheduler','system user')")
        processes = self.cursor.fetchall()
        processList = []
        self.activeProcessList = []
        for p in processes:
            pro = {}
            if not p[7]:
                continue
            pro["id"] = p[0]
            pro["user"] = p[1]
            pro["host"] = p[2]
            pro["db"] = p[3]
            pro["command"] = p[4]
            pro["time"] = p[5]
            pro["state"] = p[6]
            pro["info"] = p[7]
            
            process = Process(pro)
            processList.append(process)
            if pro["command"] != "Sleep" and pro["command"] != "Daemon":
                self.activeProcessList.append(pro)
                
        self.ActiveProcessCount = len(self.activeProcessList) 
        self.processList = processList.sort(key=lambda x:x["time"],reverse=True)
        self.activeProcessList.sort(key=lambda x:x["time"],reverse=True)
        return processList

    #is_now: 1 current, 0 old, -1 (current - old) 
    def getStatus(self,name,is_now,is_float=0):
        try:
            if is_now:
                v = self.globalStatus[name]
            else:
                v = self.globalStatus_old[name]
        except KeyError:
            return False
 
        if is_float:
            return float(v)
        else:
            return v
        
    def getStatusDiff(self,name,is_float=0):
        try:
            v1 = self.globalStatus[name]
            v2 = self.globalStatus_old[name]
        except KeyError:
            return False
        
        if is_float:
            if v1 == v2:
                return float(1)
            else:
                return float(v1) - float(v2)
        else:
            if v1 == v2:
                return float(1)
            else:
                return long(v1) - long(v2)
            
    def _diffGlobalWaitEvents(self,eventList,eventList_old):
        diffEventsList = []
        total_wait = 0
        for e in eventList:
            for w in eventList_old:
                if w.name == e.name:
                    e.sum_wait -= w.sum_wait
                    break
            if e.sum_wait > 0:
                total_wait += e.sum_wait
                diffEventsList.append(e)
                
        self.total_wait = total_wait
        diffEventsList.sort(key=lambda x:x.sum_wait, reverse=True)

        return diffEventsList
    
    def _diffGlobalIOByFiles(self,eventList,eventList_old):
        diffFilesList = []
        total_file_io = 0
        for e in eventList:
            for w in eventList_old:
                if w.name == e.name:
                    e.total_op -= w.total_op
                    break
            if e.total_op > 0:
                total_file_io += e.total_op
                diffFilesList.append(e)
                
        self.total_file_io = total_file_io       
        diffFilesList.sort(key=lambda x:x.total_op, reverse=True)

        return diffFilesList


    def getVar(self,name,is_float=0):
        try:
            v = self.globalVars[name]
            if is_float:
                return float(v)
            else:
                return v
        except KeyError:
            return False
        
    def refreshGlobalStatus(self):
        self.globalStatus_old = self.globalStatus
        self.globalStatus = self._queryGlobalStatus()
        
    def refreshProcessList(self):
        self._processList()
        
    def refreshWaitEvents(self):
        self.instanceWaitEventList_old = self.instanceWaitEventList
        self.instanceWaitEventList = self._queryInstanceWaitEvents()
        
        self.globalWaitEventList_old = self.globalWaitEventList
        self.globalWaitEventList = self._queryGlobalWaitEvents()
        
        self.fileIOByEventList_old = self.fileIOByEventList
        self.fileIOByEventList = self._queryGlobalFileIOByEvents()
        
        self.fileIOByFileList_old = self.fileIOByFileList
        self.fileIOByFileList = self._queryGlobalFileIOByFile()
        self.globalWaitEventsDiffList = self._diffGlobalWaitEvents(self.globalWaitEventList,self.globalWaitEventList_old)
        self.fileIOFilesDiffList = self._diffGlobalIOByFiles(self.fileIOByFileList,self.fileIOByFileList_old)

    def destroy(self):
        self.cursor.close()
        self.conn.close()

class Variable:
    def __init__(self,name,value):
        self.key = name
        self.value = value

class Event:
    def __init__(self,e):
        self.type = e["type"]
        if not self.type == "file":
            self.name = e["event_name"]
        else: self.name = e["file_name"]
            
    def getName(self):
        return self.name
    
class WaitEvent(Event):
    def __init__(self,e):
        Event.__init__(self, e)
        self.count_star = long(e["count_star"])
        self.sum_wait = long(e["sum_wait"])
        self.avg_wait = long(e["avg_wait"])
        self.max_wait = long(e["max_wait"])
        self.min_wait = long(e["min_wait"])
        if self.type == "instance":
            self.object_instance_begin = e["object_instance_begin"]
    
class FileEvent(Event):
    def __init__(self,e):
        Event.__init__(self, e)
        self.count_read = long(e["count_read"])
        self.count_write = long(e["count_write"])
        self.bytes_read = long(e["bytes_read"])
        self.bytes_write = long(e["bytes_write"])
        self.total_op = long(e["total_op"])
        self.total_bytes = long(e["total_bytes"])
        
        if self.type == "file":
            self.file_name = e["file_name"]
            
            
class MySQLHost:
    def __init__(self,h):
        self.host = h["host"]
        self.user = h["user"]
        self.passwd = h["pass"]
        self.port = h["port"]
        self.host_id = int(h["host_id"])          

class Snapshot:
    def __init__(self,h):
        self.host = h
        
    def setSnapID(self,snap_id):
        self.snapshot_id = snap_id
        
    def setSnapTime(self,snapTime):
        self.snapTime = snapTime
        
    def getSnapID(self):
        return self.snapshot_id

    def getSnapTime(self):
        return self.snapTime
        
    def setReported(self):
        dbt.cursor.execute("update myperf_snapshot set report_time = now() where id = %s",(self.snapshot_id,))
        
    def snap(self):
        try:
            dbs = DatabaseSource(self.host.host,self.host.user,self.host.passwd,int(self.host.port))
            dbs.initDS()
            dbs.initPerf()
            self._save(dbs)
            dbt.conn.commit()
        except Exception,e:
            print e
            dbt.conn.rollback()
            return False
        return True
        
    def _save(self,dbs):
        self._createSnap()
        self._refreshVariables(dbs)
        self._saveStatus(dbs)
        if dbs.version_main >=5 and dbs.version_middle >=5:
            self._saveWaitEvent(dbs)
            self._saveFileIO(dbs) 
            
    def _refreshVariables(self,dbs):
        dbt.cursor.execute("delete from myperf_source_host_variables where host_id = %s",(self.host.host_id,))
        for v in dbs.globalVars:
            dbt.cursor.execute("insert into myperf_source_host_variables(host_id,variable_name,variable_value) values(%s,%s,%s)",(self.host.host_id,v,dbt.getVar(v)))
            
    def _saveStatus(self,dbs):
        for s in dbs.globalStatus:
            dbt.cursor.execute("insert into myperf_snapshot_status(snap_id,status_name,status_value) values(%s,%s,%s)",(self.snapshot_id,s,dbs.getStatus(s, 1)))        

    def _saveWaitEvent(self,dbs):
        l_size = len(dbs.globalWaitEventList)
        i = 0
        while i < l_size:
            e = dbs.globalWaitEventList[i]
            dbt.cursor.execute("""insert into myperf_snapshot_events_waits_summary_global_by_event_name
                                (snap_id,event_name,count_star,sum_timer_wait,min_timer_wait,avg_timer_wait,max_timer_wait) 
                          values(%s,%s,%s,%s,%s,%s,%s)""",(self.snapshot_id,e.name,e.count_star,e.sum_wait,e.min_wait,e.avg_wait,e.max_wait))
            i = i + 1
                        
        l_size= len(dbs.instanceWaitEventList)
        i = 0
        while i < l_size:
            e = dbs.instanceWaitEventList[i]
            dbt.cursor.execute("""insert into myperf_snapshot_events_waits_summary_by_instance
                                (snap_id,event_name,object_instance_begin,count_star,sum_timer_wait,min_timer_wait,avg_timer_wait,max_timer_wait) 
                          values(%s,%s,%s,%s,%s,%s,%s,%s)""",(self.snapshot_id,e.name,e.object_instance_begin,e.count_star,e.sum_wait,e.min_wait,e.avg_wait,e.max_wait))
            i = i + 1
        
            
    def _saveFileIO(self,dbs):
        l_size = len(dbs.fileIOByEventList)
        i = 0
        while i < l_size:
            e = dbs.fileIOByEventList[i]
            dbt.cursor.execute("""insert into myperf_snapshot_file_summary_by_event_name
                                (snap_id,event_name,count_read,count_write,sum_number_of_bytes_read,sum_number_of_bytes_write) 
                          values(%s,%s,%s,%s,%s,%s)""",(self.snapshot_id,e.name,e.count_read,e.count_write,e.bytes_read,e.bytes_write))
            i = i + 1
                        
        l_size= len(dbs.fileIOByFileList)
        i = 0
        while i < l_size:
            e = dbs.fileIOByFileList[i]
            dbt.cursor.execute("""insert into myperf_snapshot_file_summary_by_instance
                                (snap_id,file_name,event_name,count_read,count_write,sum_number_of_bytes_read,sum_number_of_bytes_write) 
                          values(%s,%s,%s,%s,%s,%s,%s)""",(self.snapshot_id,e.file_name,"",e.count_read,e.count_write,e.bytes_read,e.bytes_write))
            i = i + 1

            
    def _createSnap(self):
        dbt.cursor.execute("insert into myperf_snapshot(host_id,snap_time) values(%s,now())",(self.host.host_id,))
        dbt.cursor.execute("select last_insert_id()")
        id = dbt.cursor.fetchone()[0]
        self.snapshot_id = id
        
            
    def getStatus(self):
        dbt.cursor.execute("select status_name,status_value from myperf_snapshot_status where snap_id = %s",(self.snapshot_id,))
        rs = dbt.cursor.fetchall()
        sts = {}
        for r in rs:
            sts[r[0]] = r[1] 
        return sts
    
    def getVariables(self):
        dbt.cursor.execute("select variable_name,variable_value from myperf_source_host_variables where host_id = %s",(self.host.host_id,))
        rs = dbt.cursor.fetchall()
        vars = {}
        for r in rs:
            vars[r[0]] = r[1]
        return vars
    
    def getGlobalWaitEventList(self):
        dbt.cursor.execute("select event_name,count_star,sum_timer_wait,avg_timer_wait,max_timer_wait,min_timer_wait from myperf_snapshot_events_waits_summary_global_by_event_name where snap_id = %s order by sum_timer_wait desc",(self.snapshot_id,))
        rs = dbt.cursor.fetchall()
        eventList = []
        for r in rs:
            e = {}
            e["type"] = "global"
            e["event_name"] = r[0]
            e["count_star"] = r[1]
            e["sum_wait"] = r[2]
            e["avg_wait"] = r[3]
            e["max_wait"] = r[4]
            e["min_wait"] = r[5]
            event = WaitEvent(e)
            eventList.append(event)
        return eventList
    
    def getFileIOByFileList(self):
        dbt.cursor.execute("select file_name,count_read,count_write,sum_number_of_bytes_read,sum_number_of_bytes_write,(count_read + count_write) total_op,(sum_number_of_bytes_read + sum_number_of_bytes_write) total_bytes from myperf_snapshot_file_summary_by_instance where snap_id = %s order by total_op desc",(self.snapshot_id,))
        rs = dbt.cursor.fetchall()
        eventList = []
        for r in rs:
            e = {}
            e["type"] = "file"
            e["file_name"] = r [0]
            e["count_read"] = r[1]
            e["count_write"] = r[2]
            e["bytes_read"] = r[3]
            e["bytes_write"] = r[4]
            e["total_op"] = r[5] 
            e["total_bytes"] = r[6] 
                        
            event = FileEvent(e)
            eventList.append(event)
        return eventList
    
class Process:
    def __init__(self,p):
        self.id = p["id"]
        self.user = p["user"]
        self.host = p["host"]
        self.db = p["db"]
        self.command = p["command"]
        self.time = p["time"]
        self.state = p["state"]
        self.info = p["info"]
        self.rows_sent = 0
        self.rows_examined = 0
        self.rows_read = 0
    
    def __getitem__(self,name):
        if name == "time":
            return self.time
        else:
            return self.id
    
    def isActive(self):
        if self.command != "Sleep" and self.command != "Daemon":
            return 1
        else:
            return 0
        
    def isSysProcess(self):
        if self.user == "event_scheduler" or self.user == "system user":
            return 1
        else:
            return 0

class Report:
    def __init__(self):
        self.title = "MySQL Performance Report"

    def _printFormatStr(self,v,len):
        fStr = ""
        if v/1024/1024 < 1:
            fStr = "%*.2fKB" % (len,v/1024)
            print "%*.2fKB" % (len,v/1024),
        elif v/1024/1024/1024 < 1:
            fStr = "%*.2fMB" % (len,v/1024/1024)
            print "%*.2fMB" % (len,v/1024/1024),
        elif v/1024/1024/1024/1024 < 1:
            fStr = "%*.2fGB" % (len,v/1024/1024/1024)
            print "%*.2fGB" % (len,v/1024/1024/1024),
        else:
            fStr = "%*.2fTB" % (len,v/1024/1024/1024/1024)
            print "%*.2fTB" % (len,v/1024/1024/1024/1024),
        return fStr
    
    def _formatSizeStr(self,v,len):
        fStr = ""
        if v/1024/1024 < 1:
            fStr = "%*.2fKB" % (len,v/1024)
        elif v/1024/1024/1024 < 1:
            fStr = "%*.2fMB" % (len,v/1024/1024)
        elif v/1024/1024/1024/1024 < 1:
            fStr = "%*.2fGB" % (len,v/1024/1024/1024)
        else:
            fStr = "%*.2fTB" % (len,v/1024/1024/1024/1024)
        return fStr

    def _qcacheUsage(self):
        qCacheUsageStr = "     Query Cache: "
        #print "     Query Cache: ",
        if not dbs.qCache:
            qCacheUsageStr += "%15s" % "OFF"
        else:
            size = dbs.getVar("query_cache_size", 1)
            free = dbs.getStatus("Qcache_free_memory", 1, 1)
            usage = (size - free)/size*100
            #self._printFormatStr(size, 7)
            qCacheUsageStr += self._formatSizeStr(size, 8) + " total,"
            #print 'total,',
            #self._printFormatStr((size - free), 7)
            qCacheUsageStr += self._formatSizeStr((size - free), 8)
            if opt_main["mode"] == "top" and not is_windows and usage < 80:
                qCacheUsageStr += " used, usage: \033[7m%6.2f%%\033[0m" % usage
                #print 'used, usage: \033[7m %6.2f%%\033[0m' % usage
            else:
                qCacheUsageStr += " used, usage: %6.2f%%" % usage
                #print 'used, usage: %7.2f%%' % usage
        
        return qCacheUsageStr       

    def _keybufferUsage(self):
        kBufUsageStr = "     Key Buffer: "
        size = dbs.getVar("key_buffer_size",1)
        used = dbs.getStatus("Key_blocks_used",1,1)*dbs.getVar("key_cache_block_size",1)
        #print '     Key Buffer: ',
        #self._printFormatStr(size, 8)
        kBufUsageStr += self._formatSizeStr(size, 9) + " total,"
        #print 'total,',
        kBufUsageStr += self._formatSizeStr(used, 8)
        #self._printFormatStr(used, 7)
        if size == 0:
            usage = 100
        else:
            usage = used/size*100
        if opt_main["mode"] == "top" and not is_windows and usage < 80:
            kBufUsageStr += " used, usage: \033[7m%6.2f%%\033[0m" % usage
            #print 'used, usage: \033[7m%7.2f%%\033[0m' % usage
        else:
            kBufUsageStr += " used, usage: %6.2f%%" % usage
            #print 'used, usage: %7.2f%%' % usage
        return kBufUsageStr
        
    def _innodbUsage(self):
        ibBufUsageStr = "     InnoDB Buffer: "
        size = dbs.getVar("innodb_buffer_pool_size",1)
        free = dbs.getStatus("Innodb_buffer_pool_pages_free",1,1)*dbs.getVar("innodb_page_size",1)
        used = size - free
        if size == 0:
            usage = 100
        else:
            usage = used/size*100
        #print '     InnoDB Buffer: ',
        ibBufUsageStr += self._formatSizeStr(size, 6)
        #self._printFormatStr(size, 5)
        ibBufUsageStr += " total,"
        #print 'total,',
        ibBufUsageStr += self._formatSizeStr(used, 8)
        #self._printFormatStr(used, 7)
        if opt_main["mode"] == "top" and not is_windows and usage < 80:
            ibBufUsageStr += " used, usage:\033[7 %6.2f%%\033[0m" % usage
            #print 'used, musage:\033[7 %7.2f%%\033[0m' % usage
        else:
            ibBufUsageStr += " used, usage: %6.2f%%" % usage
            #print 'used, usage: %7.2f%%' % usage
        return ibBufUsageStr

    def _threadCacheUsage(self):
        threadCacheUsageStr = "     Thread Cache: "
        size = dbs.getVar("thread_cache_size",1)
        used = dbs.getStatus("Threads_cached",1,1)
        if size == 0:
            usage = 100
        else:
            usage = used/size*100
        threadCacheUsageStr += "%9d total,%10d uesd, usage:" % (size,used)
        #print '     Thread Cache: %9d total,%10d uesd, usage:' % (size,used),
        if opt_main["mode"] == "top" and not is_windows and usage < 80:
            threadCacheUsageStr += "\033[7m %6.2f%%\033[0m" % usage
            #print '\033[7m %6.2f%%\033[0m' % usage
        else:
            threadCacheUsageStr += " %6.2f%%" % usage
            #print ' %6.2f%%' % usage
        return threadCacheUsageStr

    def _tableCacheUsage(self):
        tbCacheUsageStr = "     Table Cache: "
        size = dbs.getVar("table_open_cache",1)
        used = dbs.getStatus("Open_tables",1,1)
        if size == 0:
            usage = 100
        else:
            usage = used/size*100
        tbCacheUsageStr += "%10d total,%10d uesd, usage:" % (size,used)
        #print '     Table Cache: %10d total,%10d uesd, usage:' % (size,used),
        if opt_main["mode"] == "top" and not is_windows and usage < 80:
            tbCacheUsageStr += "\033[7m %6.2f%%\033[0m" % usage
            #print '\033[7m %6.2f%%\033[0m' % usage
        else:
            tbCacheUsageStr += " %6.2f%%" % usage
            #print ' %6.2f%%' % usage
        return tbCacheUsageStr
        
    def _ConnectionUsage(self):
        connUsageStr = "     Connections: "
        size = dbs.getVar("max_connections")
        used = dbs.getStatus("Threads_connected", 1, 0)
        active = dbs.getStatus("Threads_running", 1, 0)
        connUsageStr += "%10s total,%10s uesd, Active:" % (size,used)
        #print '     Connections: %10s total,%10s uesd, Active:' % (size,dbs.processCount),
        if  opt_main["mode"] == "top" and not is_windows and dbs.ActiveProcessCount > 100 :
            connUsageStr += "\033[7m%6s\033[0m" % active
            #print '\033[7m%6d\033[0m' % dbs.ActiveProcessCount
        else:
            connUsageStr += " %6s" % active
            #print ' %6d' % dbs.ActiveProcessCount
        return connUsageStr
    
    def _logicalIOPerf(self):
        logicalIOPerfStr = ""
        read = dbs.getStatusDiff("Innodb_buffer_pool_read_requests") + dbs.getStatusDiff("Key_read_requests")
        write = dbs.getStatusDiff("Innodb_buffer_pool_write_requests") + dbs.getStatusDiff("Key_write_requests")
        reads = dbs.getStatusDiff("Innodb_buffer_pool_reads") + dbs.getStatusDiff("Key_reads")
        hitratio = (read-reads)/read*100
        logicalIOPerfStr += "  Logical IO: %10d/s, read: %9d/s, write: %6d/s, HitRatio:" % ((read+write)/interval,read/interval,write/interval)
        if opt_main["mode"] == "top" and not is_windows and hitratio < 80:
            logicalIOPerfStr += " \033[7m %6.2f%%\033[0m" % hitratio
            #print '\033[7m %6.2f%%\033[0m' % hitratio
        else:
            logicalIOPerfStr += " %7.2f%%" % hitratio
            #print ' %6.2f%%' % hitratio
            
        return logicalIOPerfStr

    def _keyBufferPerf(self):
        keyBufPerfStr = ""
        read = dbs.getStatusDiff("Key_read_requests", 1)
        reads = dbs.getStatusDiff("Key_reads", 1)
        write = dbs.getStatusDiff("Key_write_requests", 1)
        writes = dbs.getStatusDiff("Key_writes", 1)
        hitratio = (read+write-reads-writes)/(read+write)*100
        keyBufPerfStr += "  Key Buffer: %10d/s, read: %9d/s, write: %6d/s, HitRatio:" % ((read+write)/interval,read/interval,write/interval)
        if opt_main["mode"] == "top" and not is_windows and hitratio < 80:
            keyBufPerfStr +=  " \033[7m %6.2f%%\033[0m" % hitratio
        else:
            keyBufPerfStr +=  " %7.2f%%" % hitratio 
            
        return keyBufPerfStr
        
    def _ibBufferPerf(self):
        ibBufPerfStr = ""
        read = dbs.getStatusDiff("Innodb_buffer_pool_read_requests", 1)
        write = dbs.getStatusDiff("Innodb_buffer_pool_write_requests", 1)
        reads = dbs.getStatusDiff("Innodb_buffer_pool_reads", 1)
        hitratio = (read-reads)/read*100
        ibBufPerfStr += "  Idb Buffer: %10d/s, read: %9d/s, write: %6d/s, HitRatio:" % ((read+write)/interval,read/interval,write/interval)
        if opt_main["mode"] == "top" and not is_windows and hitratio < 80:
            ibBufPerfStr +=  " \033[7m %6.2f%%\033[0m" % hitratio
        else:
            ibBufPerfStr +=  " %7.2f%%" % hitratio 
            
        return ibBufPerfStr
            
    def _connectPerf(self):
        connPerfStr = ""
        connect = dbs.getStatusDiff("Connections", 1)
        create = dbs.getStatusDiff("Threads_created", 1)
        hitratio = (connect-create)/connect*100
        connPerfStr += "  Connected: %11d/s, create: %7d/s, cached: %5d/s, HitRatio:" % (connect/interval,create/interval,(connect-create)/interval) 
        if opt_main["mode"] == "top" and not is_windows and hitratio < 80:
            connPerfStr += " \033[7m %6.2f%%\033[0m" % hitratio
        else:
            connPerfStr += " %7.2f%%" % hitratio 
            
        return connPerfStr 

    def _queryPerf(self):
        select = dbs.getStatusDiff("Com_select", 1) + dbs.getStatusDiff("Qcache_hits", 1)
        update = dbs.getStatusDiff("Com_update", 1) + dbs.getStatusDiff("Com_update_multi",1)
        insert = dbs.getStatusDiff("Com_insert", 1) + dbs.getStatusDiff("Com_insert_select",1)
        delete = dbs.getStatusDiff("Com_delete", 1) + dbs.getStatusDiff("Com_delete_multi",1)
        queryPerfStr = "  Select: %14d/s, update:%8d/s, insert: %5d/s, delete: %8d/s" % (select/interval,update/interval,insert/interval,delete/interval)
        
        return  queryPerfStr

    def _qcachePerf(self):
        hits = dbs.getStatusDiff("Qcache_hits", 1)
        inserts = dbs.getStatusDiff("Qcache_inserts", 1)
        qCachePerfStr = "  QCache: %14d/s, insert:%8d/s, hits: %7d/s, HitRatio: %7.2f%%" % ((inserts+hits)/interval,inserts/interval,hits/interval,hits/(hits+inserts)*100)
        
        return qCachePerfStr
               
    def _sortPerf(self):
        range = dbs.getStatusDiff("Sort_range") 
        scan = dbs.getStatusDiff("Sort_scan") 
        merge_passes = dbs.getStatusDiff("Sort_merge_passes")
        sortPerfStr = "  Sort: %16d/s, range:%9d/s, scan: %7d/s, MergePass: %5d/s" % ((range+scan+merge_passes)/interval,range/interval,scan/interval,merge_passes/interval)
        
        return sortPerfStr
               
    def _tmpPerf(self):
        tmpPerfStr = ""
        tmp_tables = dbs.getStatusDiff("Created_tmp_tables") 
        tmp_disk_tables = dbs.getStatusDiff("Created_tmp_disk_tables") 
        tmp_files = dbs.getStatusDiff("Created_tmp_files")
        disk_tmp_percent = tmp_disk_tables/tmp_tables*100
        tmpPerfStr += "  Tmp Tables: %10d/s, disk:%10d/s, disk/memory:" % (tmp_tables/interval,tmp_disk_tables/interval)
        if opt_main["mode"] == "top" and not is_windows and disk_tmp_percent > 10:
            tmpPerfStr += " \033[7m %8.2f%%\033[0m" % disk_tmp_percent
        else:
            tmpPerfStr += " %11.2f%%" % disk_tmp_percent
            
        return tmpPerfStr
               
    def _trafficPerf(self):
        trafficPerfStr = "  Traffic:"
        received = dbs.getStatusDiff("Bytes_received", 1)
        sent = dbs.getStatusDiff("Qcache_inserts", 1)
        traff = received + sent
        trafficPerfStr += self._formatSizeStr(traff,12) + "/s, sent:"
        trafficPerfStr += self._formatSizeStr(sent, 8) + "/s, received: "
        trafficPerfStr += self._formatSizeStr(received, 10) + "/s"
        
        return trafficPerfStr
    
    def _reportDBUsage(self):
        dbUsageStr = "-------- MySQL %20s, " % dbs.getVar("version")
        if dbs.hasInnoDB:
            dbUsageStr += " With Innodb -----------------------\n"
        else:
            dbUsageStr += " Without Innodb     -----------------------\n"
            #print 'Without Innodb     ---------------'
        dbUsageStr += self._ConnectionUsage() + "\n"
        dbUsageStr += self._threadCacheUsage() + "\n"
        dbUsageStr += self._tableCacheUsage() + "\n"
        dbUsageStr += self._qcacheUsage() + "\n"
        dbUsageStr += self._keybufferUsage() + "\n"
        if dbs.hasInnoDB:
            dbUsageStr += self._innodbUsage() + "\n"
            
        return dbUsageStr + "\n"
    
            

    def _reportDBPerf(self):
        dbPerfStr = "-------- Performance--------------------------------------------------------\n"
        dbPerfStr += self._queryPerf() + "\n"
        dbPerfStr += self._sortPerf() + "\n"
        if dbs.qCache:
            dbPerfStr += self._qcachePerf() + "\n"
        dbPerfStr +=  self._logicalIOPerf() + "\n"
        dbPerfStr += self._keyBufferPerf() + "\n"
        if dbs.hasInnoDB:
            dbPerfStr += self._ibBufferPerf() + "\n"
        dbPerfStr += self._connectPerf() + "\n"
        dbPerfStr += self._tmpPerf() + "\n"
        dbPerfStr += self._trafficPerf() + "\n"
        
        return dbPerfStr +"\n"
    
    def _reportTopWaitEvent(self,size):
        topWaitEventStr = "-------- Top %3s Wait Events ------------------------------------------------\n" % size
        topWaitEventStr += "%7s%% %20s\t%s\n" %("Percent", "Wait Time(/s)", "Event Name")
        i = len(dbs.globalWaitEventsDiffList)
        if i > 0:
            j = 0
            while j < size and j < i:
                event = dbs.globalWaitEventsDiffList[j]
                topWaitEventStr += "%7.2f%% %20s\t%s\n" %(float(event.sum_wait/dbs.total_wait), event.sum_wait/interval, event.name)
                j += 1
                
        return topWaitEventStr + "\n"

    def _reportTopIOFile(self,size):
        topBusyFileStr = "-------- Top %3s Busy Files ------------------------------------------------\n" % size
        topBusyFileStr += "%7s%% %12s\t%s\n" %("Percent","Total io(/s)", "File Name")
        i = len(dbs.fileIOFilesDiffList)
        if i > 0:
            j = 0
            while j < size and j < i:
                event = dbs.fileIOFilesDiffList[j]
                topBusyFileStr += "%7.2f%% %10s  \t%s\n" %(float(event.total_op/dbs.total_file_io), event.total_op/interval, event.file_name)
                j += 1
        
        return topBusyFileStr + "\n"

    
class SnapReport(Report):
    def __init__(self,snapshot,latest_report_snap):
        Report.__init__(self)
        self.snapShot = snapshot
        self.latest_reported_snap = latest_report_snap
        dbs.initForSnapReport(self.snapShot.getVariables(), self.snapShot.getStatus(), self.latest_reported_snap.getStatus(), 
                          self.snapShot.getGlobalWaitEventList(), self.latest_reported_snap.getGlobalWaitEventList(), 
                          self.snapShot.getFileIOByFileList(),self.latest_reported_snap.getFileIOByFileList())
        global interval
        interval = (self.snapShot.getSnapTime() - self.latest_reported_snap.getSnapTime()).seconds
        self.title = "\t%s : %s, Snapshot: %d ~ %d , Powered By isky000.com\n" % (my_name,my_version,self.latest_reported_snap.getSnapID(),self.snapShot.getSnapID())
        self.title += "\t\tFrom %10s To %10s, %s Seconds\n" % (self.latest_reported_snap.getSnapTime(),self.snapShot.getSnapTime(),interval)
        
        
        
    def report(self):
        reportStr = ""
        try:
            if not os.path.exists(opt_main["report"]):
                print "Error: report file dir not exists,please create first!"
                return 0
                #os.mkdir(opt_main["report"])
                
            filePath = "%s/%s" % (opt_main["report"],self.snapShot.host.host)
            if not os.path.exists(filePath):
                os.mkdir(filePath)
                
            fileName = "%s-%s-%s.txt" % (self.snapShot.host.host,self.latest_reported_snap.getSnapID(),self.snapShot.getSnapID())
            reportFile = filePath + "/" + fileName
            
            f = open(reportFile,"w")
            
            reportStr += self.title + "\n"
            reportStr += self._reportDBUsage() + "\n"
            reportStr += self._reportDBPerf() + "\n"
            if dbs.version_main >=5 and dbs.version_middle >=5:
                reportStr += self._reportTopWaitEvent(10) + "\n"
                reportStr += self._reportTopIOFile(10) + "\n"
            print >> f,reportStr
            f.close()
        except Exception,e:
            print e
            return 0
        return 1
        

#Status top report info
class TopReport(Report):
    def __init__(self):
        Report.__init__(self)
        self.title = "MySQL RealTime Status Show"
    
    def _reportSysInfo(self):
        print "\t%s : %s, Interval: %ss, Powered By isky000.com and Leo" % (my_name,my_version,interval)
        if not (is_windows):
            os.system("uptime")    

    def _reportActiveSession(self):
        actSessionStr = "-------- Active Session-----------------------------------------------------\n"
        actSessionStr += "  %-30s|%-8s|%-15s|%s\n" % ("From","Time","State","Info")
        if len(dbs.activeProcessList) > 0:
            for process in dbs.activeProcessList:
                p_host = process["host"].split(":")[0]
                if process["time"] > 3:
                    actSessionStr += "  \033[7m%-30s|%-8s|%-15s|%s\033[0m\n" %(process["user"]+":"+p_host,process["time"],process["state"],process["info"])
                else:
                    actSessionStr +=  "  %-30s|%-8s|%-15s|%s\n" %(process["user"]+":"+p_host,process["time"],process["state"],process["info"])
                    
        print actSessionStr
        
    def reportBasic(self):
        if(is_windows):
            os.system("cls")
        else:
            os.system("clear")
        self._reportSysInfo()
        print "\n"
        print self._reportDBUsage()
        print self._reportDBPerf()

        
    def reportWaitEvent(self):
        if dbs.version_main >=5 and dbs.version_middle >=5:
            print self._reportTopWaitEvent(10) + "\n"
            print self._reportTopIOFile(10) + "\n"
        
    def reportActiveSession(self):
        self._reportActiveSession()
        print ""         
        
def configOption():
    usage = "usage: %prog [options] arg , e.g. %prog -uusername -ppasswd -i 1 top"
    parser = OptionParser(usage)
    parser.add_option("-H","--host",dest="host",help="HostName or IP Address of the MySQL,default 127.0.0.1")
    parser.add_option("-u","--user",dest="user",help="UserName of the MySQL you want to login")
    parser.add_option("-p","--pass",dest="passwd",help="Password of the MySQL you want to login",default="")
    parser.add_option("-P","--port",dest="port",type="int",help="Port of the MySQL ,default 3306")
    parser.add_option("-d","--database",dest="database",help="database name for save snapshot data,default: myperf")
    parser.add_option("-i","--interval",dest="interval",type="int",help="refresh time(s), default 3s")
    parser.add_option("-m","--mode",dest="mode",help="Which mode: top, snap, report. default: top")
    parser.add_option("-c","--config",dest="config",help="Config file,you can store all these arguments in the config file")
    parser.add_option("-r","--report",dest="report",help="Directory for store Report Files")
    parser.add_option("-e","--event",action="store_true", dest="event",help="if report wait event")
    parser.add_option("-s","--session",action="store_true", dest="session",help="if report active session")

    (options, args) = parser.parse_args()

    if not options.user and not options.config:
        parser.error("You must input -u or -c parameters");

    global opt_main,opt_source,opt_target
    opt_main = {}
    opt_source = {}
    opt_target = {}
    
    if options.config:
        if not os.path.isfile(options.config):
            print "your config file %s is not exists,please check it!" % options.config
            sys.exit(1)
        config = ConfigParser.ConfigParser()
        config.read(options.config)
        sections = config.sections()
        if len(sections):
            for s in sections:
                items = config.items(s)
                if s == "main":
                    for item in items:
                        opt_main[item[0]] = item[1]
                elif s == "source":
                    for item in items:
                        opt_source[item[0]] = item[1]
                elif s == "target":
                    for item in items:
                        opt_target[item[0]] = item[1]
    if options.mode:
        opt_main["mode"] = options.mode
        if not opt_main.has_key("mode"):
            opt_main["mode"] = "top"

    if opt_main["mode"] == "top":
        if options.interval:
            opt_main["interval"] = options.interval
        else:
            if not opt_main.has_key("interval"):
                opt_main["interval"] = 3

        global interval
        interval = int(opt_main["interval"])
        
        if options.user:
            opt_source["user"] = options.user
        else:
            if not opt_source.has_key("user"):
                opt_source["user"] = "root"
                
        if options.passwd:
            opt_source["pass"] = options.passwd
        else:
            if not opt_source.has_key("pass"):
                opt_source["pass"] = ""
                
        if options.host:
            opt_source["host"] = options.host
        else:
            if not opt_source.has_key("host"):
                opt_source["host"] = "127.0.0.1"
                
        if options.port:
            opt_source["port"] = options.port
        else:
            if not opt_source.has_key("port"):
                opt_source["port"] = 3306
                
        if options.event:
            opt_main["event"] = 1
        else:
            if not opt_main.has_key("event"):
                opt_main["event"] = 0
                
        if options.session:
            opt_main["session"] = 1
        else:
            if not opt_main.has_key("session"):
                opt_main["session"] = 0
                
    elif opt_main["mode"] == "snap":
        if options.user:
            opt_target["user"] = options.user
        else:
            if not opt_target.has_key("user"):
                opt_target["user"] = "root"
                
        if options.passwd:
            opt_target["pass"] = options.passwd
        else:
            if not opt_target.has_key("pass"):
                opt_target["pass"] = ""
                
        if options.host:
            opt_target["host"] = options.host
        else:
            if not opt_target.has_key("host"):
                opt_target["host"] = "127.0.0.1"
                
        if options.port:
            opt_target["port"] = options.port
        else:
            if not opt_target.has_key("port"):
                opt_target["port"] = 3306
                
        if options.database:
            opt_target["database"] = options.database
        else:
            if not opt_target.has_key("database"):
                opt_target["database"] = "myperf"
    else:
        if options.report:
            opt_main["report"] = options.report
        else:
            if not opt_main.has_key("report"):
                opt_main["report"] = "." 

def main():
    configOption()
    global dbt,dbs
    if opt_main["mode"] == "top":                
        dbs = DatabaseSource(opt_source["host"],opt_source["user"],opt_source["pass"],int(opt_source["port"]))
        dbs.initDS()
        dbs.initPerf()
        try:
            while True:
                time.sleep(interval)
                dbs.refreshGlobalStatus()
                if opt_main["event"]:
                    dbs.refreshWaitEvents()
                if opt_main["session"]:
                    dbs.refreshProcessList()
                report = TopReport()
                report.reportBasic()
                if opt_main["event"]:
                    report.reportWaitEvent()
                if opt_main["session"]:
                    report.reportActiveSession()
                
        except KeyboardInterrupt:
            print '     '
            dbs.destroy()        
    elif opt_main["mode"] == "snap":
        dbt = DatabaseSource(opt_target["host"],opt_target["user"],opt_target["pass"],int(opt_target["port"]))
        dbt.initDS()
        try:
            dbt.conn.select_db(opt_target["database"])
            host_cnt = dbt.cursor.execute("select id,host,user,pass,port from myperf_source_host")
            if host_cnt > 0:
                hosts = dbt.cursor.fetchall()
    
                for h in hosts:
                    host = {}
                    host["host_id"] = h[0]
                    host["host"] = h[1]
                    host["user"] = h[2]
                    host["pass"] = h[3]
                    host["port"] = h[4]
                    
                    hst = MySQLHost(host)
                    
                    snapshot = Snapshot(hst)
                    ok = snapshot.snap()
                    if ok:
                        print "%s: Snap successful" % host["host"]
                    else:
                        print "%s: Snap unsuccessful" % host["host"]
        except KeyboardInterrupt:
            print "You canceled it."
            dbt.destroy()
        
    elif opt_main["mode"] == "report":
        dbt = DatabaseSource(opt_target["host"],opt_target["user"],opt_target["pass"],int(opt_target["port"]))
        dbt.initDS()
        try:
            dbt.conn.select_db(opt_target["database"])
            host_cnt = dbt.cursor.execute("select id,host,user,pass,port from myperf_source_host")
            if host_cnt > 0:
                hosts = dbt.cursor.fetchall()
    
                for h in hosts:
                    host = {}
                    host["host_id"] = h[0]
                    host["host"] = h[1]
                    host["user"] = h[2]
                    host["pass"] = h[3]
                    host["port"] = h[4]
                    
                    hst = MySQLHost(host)
                    
                    dbs = DatabaseSource(hst.host,hst.user,hst.passwd,int(hst.port))
                    last_reported_snap_id = 0
                    
                    has_last_report_snap = dbt.cursor.execute("select id,snap_time from myperf_snapshot where report_time is not null and host_id = %s order by snap_time desc limit 1",(hst.host_id,))
                    if has_last_report_snap:
                        rs = dbt.cursor.fetchone()
                        last_reported_snap_id = rs[0]
                        latest_reported_snap = Snapshot(hst)
                        latest_reported_snap.setSnapTime(rs[1])
                        latest_reported_snap.setSnapID(last_reported_snap_id)
                    
                        
                    snap_cnt = dbt.cursor.execute("select id,snap_time from myperf_snapshot where report_time is null and host_id = %s order by snap_time",(hst.host_id,))
                    if snap_cnt:
                        snapList = []
                        rs = dbt.cursor.fetchall()
                        for r in rs:
                            snap = Snapshot(hst)
                            snap.setSnapID(r[0])
                            snap.setSnapTime(r[1])
                            snapList.append(snap)
                            
                        if last_reported_snap_id == 0:
                            latest_reported_snap = snapList[0]
                            latest_reported_snap.setReported()
                            snapList.remove(latest_reported_snap)
                            
                        lSize = len(snapList)
                        i = 0
                        while i < lSize:
                            snap = snapList[i]
                            report = SnapReport(snap,latest_reported_snap)
                            ok = report.report()
                            snap.setReported()
                            dbt.conn.commit()
                            if ok:
                                print "%25s: Report Snapshot %5s ~ %5s successful" % (hst.host,latest_reported_snap.getSnapID(),snap.getSnapID())
                            else:
                                print "%25s: Report Snapshot %5s ~ %5s unsuccessful" % (hst.host,latest_reported_snap.getSnapID(),snap.getSnapID())
                            latest_reported_snap = snap
                            i += 1
                            
        except KeyboardInterrupt:
            print "You canceled it."
            dbt.conn.rollback()
            dbt.destroy()
    else:
        print "mode must be one of these three: top, snap, report!"
        sys.exit(1);

if __name__ == "__main__":  
    main()
